在核心交易分析中完成如下指标的计算

1.数据预处理，在明细事实拉链表处理时不太方便，能够作一张中间表，
  dws_trade_orders_day其表结构和加工以下
  DROP TABLE IF EXISTS dws.dws_trade_orders_day;
CREATE TABLE IF NOT EXISTS dws.dws_trade_orders_day
(day_dtstringCOMMENT'日期：yyyy-MM-dd',
day_cntdecimal(10,0)comment'日订单笔数',
day_sumdecimal(10,0)COMMENT'日订单总额')
COMMENT'日订单统计表';

SELECT dt,
count(*)cnt,
sum(totalMoney) sm
FROM
(SELECT DISTINCT orderid,
dt,
totalMoney
FROM dwd.dwd_trade_orders
WHERE status>=0
AND dataFlag='1')tmp
GROUP BY dt;

INSERT OVERWRITE TABLE dws.dws_trade_orders_day
SELECT dt,
count(*)cnt,
sum(totalMoney) sm
FROM
(SELECT DISTINCT orderid,
dt,
totalMoney
FROM dwd.dwd_trade_orders
WHERE status>=0
AND dataFlag='1')tmp
GROUP BY dt;
 
 SELECT * FROM dws.dws_trade_orders_day
WHERE day_dt BETWEEN '2020-01-01' AND '2020-12-31';

 2.指标1，统计2020年每一个季度的销售订单笔数、订单总额
   先建立ads指标表：dws_trade_orders_quarter
   DROP TABLE IF EXISTS dws.dws_trade_orders_quarter;
   CREATE TABLE IF NOT EXISTS dws.dws_trade_orders_quarter(
YEAR string COMMENT '年份',
QUARTER string COMMENT '季度',
cnt decimal(10,0) COMMENT '订单总笔数',
SUM decimal(10,0) COMMENT '订单总额') COMMENT '季度订单统计表';
   
with tmp as
(SELECT substr(day_dt, 0, 4) YEAR,
CASE WHEN substr(day_dt, 6, 2)="01"
OR substr(day_dt, 6, 2)="02"
OR substr(day_dt, 6, 2)="03" THEN "1" WHEN substr(day_dt, 6, 2)="04"
OR substr(day_dt, 6, 2)="05"
OR substr(day_dt, 6, 2)="06" THEN "2" WHEN substr(day_dt, 6, 2)="07"
OR substr(day_dt, 6, 2)="08"
OR substr(day_dt, 6, 2)="09" THEN "3" WHEN substr(day_dt, 6, 2)="10"
OR substr(day_dt, 6, 2)="11"
OR substr(day_dt, 6, 2)="12" THEN "4" end as QUARTER,
day_cnt,
day_sum
FROM dws.dws_trade_orders_day)
INSERT OVERWRITE TABLE dws.dws_trade_orders_quarter 
SELECT YEAR,
QUARTER,
sum(day_cnt),
sum(day_sum)
FROM tmp
GROUP BY YEAR,QUARTER;
 
 3.统计2020年每月的销售订单笔数、订单总额
  先建立ads指标表：dws_trade_orders_month
  DROP TABLE IF EXISTS dws.dws_trade_orders_month;
CREATE TABLE IF NOT EXISTS dws.dws_trade_orders_month(
year string COMMENT '年份',
MONTH string COMMENT '月份',
month_cnt decimal COMMENT '月订单总笔数',
month_sum decimal COMMENT '月订单总额') COMMENT '月订单统计表';
  
  WITH tmp AS
(SELECT substr(day_dt, 0, 4) YEAR,
substr(day_dt, 6, 2) MONTH,
day_cnt,
day_sum
FROM dws.dws_trade_orders_day)
INSERT OVERWRITE TABLE dws.dws_trade_orders_month
SELECT YEAR,
MONTH,
sum(day_cnt) month_cnt,
sum(day_sum) month_sum
FROM tmp
GROUP BY YEAR,MONTH;
 
 4.统计2020年每周（周一到周日）的销售订单笔数、订单总额
建立ads层指标表：dws_trade_orders_week
利用到日期函数weekofyear
DROP TABLE IF EXISTS dws.dws_trade_orders_week;
CREATE TABLE IF NOT EXISTS dws.dws_trade_orders_week(
YEAR string COMMENT '年份',
WEEK string COMMENT '一年中的第几周',
week_cnt decimal COMMENT '周订单总笔数',
week_sum decimal COMMENT '周订单总额') COMMENT '周订单统计表';
 
 5.统计2020年国家法定节假日、休息日、工做日的订单笔数、订单总额
建立日期信息维表：dim_day_info 并录入节假日信息数据
(数据每一年都不同，须要国务院通知的公告，因此按期手动维护)
   drop table if exists dim.dim_day_info;
create table if not exists dim.dim_day_info(
day_dt string comment '日期',
is_holidays int comment '节假日标识： 0不是 1是',
is_workday int comment '工做日标识 0不是 1是'
) comment '日期信息表';

-- 统计2020节假日的订单笔数，订单总额
   SELECT nvl(sum(day_cnt), 0),nvl(sum(day_sum), 0)
FROM dws.dws_trade_orders_day A
LEFT JOIN dim.dim_day_info B ON A.day_dt = B.day_dt
WHERE B.is_holidays = 1;
-- 统计2020年休息日的订单笔数，订单总额
   SELECT nvl(sum(day_cnt), 0),nvl(sum(day_sum), 0)
FROM dws.dws_trade_orders_day A
LEFT JOIN dim.dim_day_info B ON A.day_dt = B.day_dt
WHERE B.is_workday = 0;
-- 统计2020节工做日的订单笔数，订单总额
   SELECT nvl(sum(day_cnt), 0),nvl(sum(day_sum), 0)
FROM dws.dws_trade_orders_day A
LEFT JOIN dim.dim_day_info B ON A.day_dt = B.day_dt
WHERE B.is_workday = 1;
 